SQL: Logical Operators

IN

IN

The operator in the spotlight today in IN.

I like to think of IN as an alternative to using multiple OR conditions. When using IN, what we're essentially doing is creating a list of values to compare against all rows of a specified column. Using IN instead of multiple OR statements cleans up the query which makes it easier to read and easier to troubleshoot.

SQL Syntax: Using IN

Example:

Customers Table

First_Name | Last_Name | Cust_State
-----------|-----------|---------
Alice      | Johnson   | AZ
Bob        | Smith     | AK
Raymond    | Barone    | NY
Michael    | Scott     | PA
John       | Dow       | FL
Bob        | Jacobson  | OK

IN

Find the first and last names of all customers who live in Arizona, Florida, and New York

Query

SELECT First_Name, Last_Name
FROM Customers
WHERE Cust_State IN ('AZ','FL','NY')

Result

First_Name | Last_Name 
-----------|-----------
Alice      | Johnson   
Raymond    | Barone     
John       | Dow

OR

The same results can be accomplished using OR, but notice how much longer this query is

Query

SELECT First_Name, Last_Name
FROM Customers
WHERE Cust_State = 'AZ'
   OR Cust_State = 'FL'
   OR Cust_State = 'NY'

Result

First_Name | Last_Name 
-----------|-----------
Alice      | Johnson   
Raymond    | Barone     
John       | Dow

Wrapping Up

The logical operator IN is a powerful tool to simplify complex conditions and make our queries easier to read and troubleshoot. It is a must have tool that every analyst should know.